using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

using phorm.config;
using phorm.database.core;

namespace phorm.database.mssql
{
    /// <summary>
    /// Microsoft SQL Database static connection object.
    /// </summary>
    static class MSSQLDatabase
    {
        /// <summary>
        /// private connection object.
        /// </summary>
        private static SqlConnection _connection = null;
        /// <summary>
        /// private transaction object used for transactions.
        /// </summary>
        private static SqlTransaction _transaction;
        /// <summary>
        /// Boolean set to true if a transaction should be used.
        /// </summary>
        private static bool _isTransaction = false;
        /// <summary>
        /// Used in testing to emulate iteration of auto_increment
        /// </summary>
        private static int counter = 1;

        /// <summary>
        /// Starts a SqlConnection if a transaction has not been started
        /// </summary>
        /// <returns>The connection link</returns>
        public static SqlConnection connection()
        {
            if (!MSSQLDatabase._isTransaction)
            {
                MSSQLDatabase._connection = new SqlConnection(MSSQLDatabase.buildConnectionString());
                MSSQLDatabase._connection.Open();
                MSSQLDatabase.nonQuery(CRUD.selectDatabase(PhormConfig.phormParams.dbName));
                MSSQLDatabase.nonQuery(CRUD.setQuotes());
            }
            return MSSQLDatabase._connection;
        }

        /// <summary>
        /// Starts a transaction connection and overrides an existing connection
        /// </summary>
        /// <returns>The connection link</returns>
        public static SqlConnection transactionConnection()
        {
            MSSQLDatabase._connection = new SqlConnection(MSSQLDatabase.buildConnectionString());
            MSSQLDatabase._connection.Open();
            MSSQLDatabase._transaction = MSSQLDatabase._connection.BeginTransaction();
            MSSQLDatabase._isTransaction = true;
            return MSSQLDatabase._connection;
        }

        /// <summary>
        /// Creates a SQL Server connection string based on the parameters in the configuration
        /// </summary>
        /// <returns>A SQL Server connection string</returns>
        private static string buildConnectionString()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.PersistSecurityInfo = false;
            builder.UserID = PhormConfig.phormParams.dbUsername;
            builder.Password = PhormConfig.phormParams.dbPassword;
            builder.InitialCatalog = PhormConfig.phormParams.dbName;
            builder["Server"] = PhormConfig.phormParams.dbHost;

            return builder.ConnectionString;
        }

        /// <summary>
        /// Runs a query that returns data
        /// </summary>
        /// <param name="query">The query to run</param>
        /// <returns>The data returned by the query</returns>
        public static SqlDataReader query(String query)
        {
            SqlDataReader retVal = null;
            if (query != null)
            {
                try
                {
                    SqlCommand cmd = MSSQLDatabase._connection.CreateCommand();
                    cmd.CommandText = query;
                    retVal = cmd.ExecuteReader();
                }
                catch (Exception e)
                {
                    Console.WriteLine(query);
                    Console.WriteLine("GO");
                    Console.WriteLine(e.Message);
                }
            }
            return retVal;
        }

        /// <summary>
        /// Runs a query that does not return data.
        /// </summary>
        /// <example>CREATE, UPDATE, DROP, TRUNCATE queries</example>
        /// <param name="query">The query</param>
        public static void nonQuery(String query)
        {
            if (query != null)
            {
                try
                {
                    SqlCommand cmd = MSSQLDatabase._connection.CreateCommand();
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine(query);
                    Console.WriteLine("GO");
                    Console.WriteLine(e.Message);
                }
            }
        }

        /// <summary>
        /// Runs a query that needs an ID returned
        /// </summary>
        /// <example>An INSERT query</example>
        /// <param name="query">The query</param>
        /// <returns>The ID of the row run by the query</returns>
        public static object idQuery(String query)
        {
            object retVal = null;
            if (query != null)
            {
                try
                {
                    SqlCommand cmd = MSSQLDatabase._connection.CreateCommand();
                    cmd.CommandText = query;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        cmd.CommandText = "SELECT SCOPE_IDENTITY()";
                        SqlDataReader reader = cmd.ExecuteReader();
                        reader.Read();
                        retVal = reader[0];
                        reader.Close();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(query);
                    Console.WriteLine("GO");
                    Console.WriteLine(e.GetType().ToString() + ": " + e.Message);
                    retVal = MSSQLDatabase.counter++;
                }
            }
            return retVal;
        }

        /// <summary>
        /// Close the connection and set to null.
        /// </summary>
        public static void finish()
        {
            if (!MSSQLDatabase._isTransaction && MSSQLDatabase._connection != null)
            {
                MSSQLDatabase._connection.Close();
                MSSQLDatabase._connection = null;
            }
        }

        /// <summary>
        /// Attempt to commit the transaction else rollback. Perform clean-up.
        /// </summary>
        public static void commit()
        {
            try
            {
                MSSQLDatabase._transaction.Commit();
            }
            catch
            {
                MSSQLDatabase._transaction.Rollback();
            }
            finally
            {
                MSSQLDatabase._isTransaction = false;
                MSSQLDatabase.finish();
            }
        }

        /// <summary>
        /// Rollback the transaction and perform clean-up.
        /// </summary>
        public static void rollback()
        {
            MSSQLDatabase._transaction.Rollback();
            MSSQLDatabase._isTransaction = false;
            MSSQLDatabase.finish();
        }
    }
}